package com.tdam2014.grupo02.DataBase;
import java.util.ArrayList;
import java.util.Date;

import com.tdam2014.grupo02.Comment;
import com.tdam2014.grupo02.Folder;
import com.tdam2014.grupo02.Picture;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

    String createTableFolders = "CREATE  TABLE Folders (idFolder INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , folderName TEXT NOT NULL , folderDescrption TEXT, folderPhoto TEXT, folderFlickrId TEXT)";

    String createTablePictures = "CREATE  TABLE Pictures (idPicture INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , picName TEXT NOT NULL , picDate DATETIME, picDescription TEXT, picFlickrId TEXT, idFolder INTEGER NOT NULL )";

    String createTableComments = "CREATE  TABLE Comments (idComment INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , commentUser TEXT NOT NULL , commentMessage TEXT NOT NULL , commentDate TEXT, commentFlickrId TEXT, idPicture TEXT NOT NULL )";
    
    public DBHelper(Context contexto, String nombre,
                               CursorFactory factory, int version) {
        super(contexto, nombre, factory, version);
    }    
 
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createTablePictures);
        db.execSQL(createTableComments);
        db.execSQL(createTableFolders);
        
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int versionAnterior, int versionNueva) 
    {
    	db.execSQL("DROP TABLE IF EXISTS Folders");
    	db.execSQL("DROP TABLE IF EXISTS Pictures");
    	db.execSQL("DROP TABLE IF EXISTS Comments");
    	
    	onCreate(db);
    	
    }
    
    public void newFolder (Folder folder, Context context)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	ContentValues cv = new ContentValues();
    	cv.put("folderName", folder.getFolderName());
    	cv.put("folderDescrption", folder.getFolderDescription());
    	cv.put("folderFlickrId", folder.getFolderFlickrId());
    	db.insert("Folders", "folderName", cv);

		db.close();
    }
    public void newPicture (Picture pic, Context context)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	ContentValues cv = new ContentValues();
    	cv.put("picName", pic.getPictureName());
    	cv.put("picDescription", pic.getPictureDescription());
    	cv.put("picDate", new Date(System.currentTimeMillis()).toString());
    	cv.put("idFolder", pic.getFolderID());
    	cv.put("picFlickrId", pic.getPictureFlickrId());
    	db.insert("Pictures", "picName", cv);
		db.close();
    }
    public void newComment (Comment com, Context context)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	ContentValues cv = new ContentValues();
    	cv.put("commentUser", com.getCommentName());
    	cv.put("commentMessage", com.getCommentMessage());
    	cv.put("commentDate", new Date(System.currentTimeMillis()).toString());
    	cv.put("idPicture", String.valueOf(com.getCommentPhotoId()));
    	db.insert("Comments", "commentUser", cv);
        Cursor cur = db.rawQuery("SELECT idComment, commentUser, commentMessage, commentDate, idPicture FROM Comments", null);
        cur.moveToFirst();
		db.close();
    }
   
    public ArrayList<Folder> getFolders(Context context)
    {
    	ArrayList<Folder> folders = new ArrayList<Folder>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cur = db.rawQuery("SELECT idFolder, folderName, folderDescrption, folderFlickrId FROM Folders" , null);
    	cur.moveToFirst();
    	do
    	{
    			Folder fol = new Folder(cur.getString(cur.getColumnIndex("folderName")), cur.getString(cur.getColumnIndex("folderDescrption")), "", cur.getString(cur.getColumnIndex("folderFlickrId")));
    			fol.setFolderId (cur.getString(cur.getColumnIndex("idFolder")));
    			folders.add(fol);
        }
    		while(cur.moveToNext());	
    	return folders;
        
    }
    public Folder getFolder(Context context, int idFolder)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
        String[] params=new String[]{String.valueOf(idFolder)};
        Cursor cur = db.rawQuery("SELECT idFolder, folderName, folderDescrption, folderFlickrId FROM Folders WHERE idFolder =?", params);
    	cur.moveToFirst();
    	Folder fol = new Folder(cur.getString(cur.getColumnIndex("folderName")), cur.getString(cur.getColumnIndex("folderDescrption")), "", cur.getString(cur.getColumnIndex("folderFlickrId")));
    	fol.setFolderId (cur.getString(cur.getColumnIndex("idFolder")));
        return fol;  
    }
    public ArrayList<Picture> getPictures(Context context,int idFolder)
    {
    	ArrayList<Picture> pictures = new ArrayList<Picture>();
        SQLiteDatabase db = this.getReadableDatabase();
        String[] params=new String[]{String.valueOf(idFolder)};
        Cursor cur = db.rawQuery("SELECT idPicture, picName, picDescription, picDate, picFlickrId, idFolder FROM Pictures WHERE idFolder =?", params);
        cur.moveToFirst();
        if(cur.getCount()>0)
        {
        	do
        	{
        			String name, description, date, flickrId;
        			int codFolder;
        			name = cur.getString(cur.getColumnIndex("picName"));
        			description = cur.getString(cur.getColumnIndex("picDescription"));
        			date = cur.getString(cur.getColumnIndex("picDate"));
        			codFolder = cur.getInt(cur.getColumnIndex("idFolder"));
        			flickrId = cur.getString(cur.getColumnIndex("picFlickrId"));
        			Picture pic = new Picture(name, description, "", new Date(System.currentTimeMillis()), "carpeta "+codFolder, codFolder, flickrId);
        			pic.setPictureId(cur.getInt(cur.getColumnIndex("idPicture")));
        			pictures.add(pic);
            }
        	while(cur.moveToNext());
        	return pictures;
        	
        }
        else
        {
        	return null;
        }
	   	
        
    }
    public Picture getPicture(Context context, int idPicture)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
        String[] params=new String[]{String.valueOf(idPicture)};
        Cursor cur = db.rawQuery("SELECT idPicture, picName, picDescription, picDate, picFlickrId, idFolder FROM Pictures WHERE idPicture =?", params);
        cur.moveToFirst();
        String name, description, date, flickrId;
		int codFolder;
		name = cur.getString(cur.getColumnIndex("picName"));
		description = cur.getString(cur.getColumnIndex("picDescription"));
		date = cur.getString(cur.getColumnIndex("picDate"));
		codFolder = cur.getInt(cur.getColumnIndex("idFolder"));
		flickrId = cur.getString(cur.getColumnIndex("picFlickrId"));
		Picture pic = new Picture(name, description, "", new Date(System.currentTimeMillis()), "carpeta "+codFolder, codFolder, flickrId);
		pic.setPictureId(cur.getInt(cur.getColumnIndex("idPicture")));
		return pic;
    }
    
    public ArrayList<Comment> getComments(Context context,int idPicture)
    {
    	ArrayList<Comment> comments = new ArrayList<Comment>();
        SQLiteDatabase db = this.getReadableDatabase();
        String[] params=new String[]{String.valueOf(idPicture)};
        Cursor cur = db.rawQuery("SELECT idComment, commentUser, commentMessage, commentDate, idPicture FROM Comments WHERE idPicture =?", params);
        cur.moveToFirst();
        if(cur.getCount()>0)
        {
        	do
        	{
        			String user, message, date;
        			int codPicture;
        			user = cur.getString(cur.getColumnIndex("commentUser"));
        			message = cur.getString(cur.getColumnIndex("commentMessage"));
        			date = cur.getString(cur.getColumnIndex("commentDate"));
        			codPicture = cur.getInt(cur.getColumnIndex("idPicture"));
        			
        			Comment com = new Comment(user, message, date, codPicture);
        			comments.add(com);
            }
        	while(cur.moveToNext());
        	return comments;
        	
        }
        else
        {
        	return null;
        }
    }
 
    public void updateFolder (ArrayList<Folder> folders, Context context)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.delete("Folders", null, null);

    	for (int i=0; i<folders.size(); i++)
    	{
    		this.newFolder(folders.get(i), context);	
    	}
    	
    }
    public void updatePictures (ArrayList<Picture> pics, Context context)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.delete("Pictures", null, null);

    	for (int i=0; i<pics.size(); i++)
    	{
    		this.newPicture(pics.get(i), context);	
    	}
    	
    }
    public void updateFolderPictures (ArrayList<Picture> pics, Context context)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.delete("Pictures", "idFolder=?", new String [] {String.valueOf(pics.get(0).getFolderID())});
    	for (int i=0; i<pics.size(); i++)
    	{
    		this.newPicture(pics.get(i), context);	
    	}
    	
    }
    public void updatePictureComments (ArrayList<Comment> comms, Context context)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.delete("Pictures", "idFolder=?", new String [] {String.valueOf(comms.get(0).getCommentPhotoId())});
    	for (int i=0; i<comms.size(); i++)
    	{
    		this.newComment(comms.get(i), context);	
    	}
    	
    }
}   
    
